
/*
Filename: A3_CreateVoting.do
Goal: Construct a comprehensive database capturing institutional investor voting behavior on individual shareholder proposals.
Contact: mjha@gsu.edu (author) and gormley@wustl.edu


Data Sources
All datasets listed below are proprietary and accessible through WRDS.

1. Voting Analytics Data
Files: VoteResults.dta, ISS_Rec.dta, MFVote.dta
Source Path: WRDS → ISS ESG → Voting Analytics
Description:
- VoteResults.dta: Aggregated voting outcomes for proposals
- ISS_Rec.dta: ISS (Institutional Shareholder Services) voting recommendations
- MFVote.dta: Individual mutual fund voting records

2. Security Identifier Linking Files
Files: Cusip_Permco.dta, Ticker_Permco.dta
Source Path: WRDS → CRSP → Annual Update → CRSP/Compustat Merged → Compustat CRSP Link
Purpose: Maps CUSIP and ticker identifiers to PERMCO (permanent company identifiers) for linking voting data to firm-level information
*/


capture log close
capture restore
set more off
clear all

global time = "month"
global level = "parent_name"
global firm = "permco" 



*************************** Part 1: Prepare ISS Results 
use "VoteResults_pseudo.dta", clear // append Global and US voting analytics results database

/* unique id */
sort countryofinc meetingid issagendaitemid ballotitemnumber seqnumber meetingdate itemonagendaid
quietly by countryofinc meetingid issagendaitemid ballotitemnumber seqnumber meetingdate itemonagendaid:  gen dup = cond(_N==1,0,_n)
count if dup>0 
drop if dup>0 
drop dup

/* add permco  */
gen permco_temp = .
foreach var in 9 8{ 
	gen cusip_all = substr(cusip,1,`var')
	merge m:1 cusip_all using "Cusip_Permco_pseudo.dta"
	drop if _merge == 2
	replace permco_temp = permco if missing(permco_temp)
	drop permco cusip_all _merge
}

merge m:1 ticker using "Ticker_Permco_pseudo.dta" 
drop if _merge == 2
replace permco_temp = permco if missing(permco_temp)
drop permco _merge

rename permco_temp permco 
drop if missing(permco)


/* add iss recomendation   */
merge m:1 companyid meetingid meetingdate issagendaitemid itemonagendaid using "ISS_Rec_pseudo.dta"  
drop if _merge == 2
drop _merge 


// from 2019 sponsor name is mentioned instead of shareholder
drop if missing(sponsor)
replace sponsor = "Shareholder" if sponsor != "Shareholder" & sponsor != "Management"


* Contentious i.e., ISS recommends against management 
gen contentious 	= 0
replace contentious = 1 if (mgmtrec=="Abstain"|mgmtrec=="Do Not Vote"|mgmtrec=="Withhold"|mgmtrec=="Against") & (issrec=="For")
replace contentious = 1 if (mgmtrec=="For") & (issrec=="Abstain"|issrec=="Do Not Vote"|issrec=="Withhold"|issrec=="Against")
replace contentious = 1 if mgmtrec=="One Year" & (strpos(issrec, "Year") & !strpos(issrec, "One"))
replace contentious = 1 if mgmtrec=="Two Years" & (strpos(issrec, "Year") & !strpos(issrec, "Two"))
replace contentious = 1 if mgmtrec=="Three Years" & (strpos(issrec, "Year") & !strpos(issrec, "Three"))

//check
gen year = yofd(meetingdate)
bysort year: count if sponsor == "Shareholder" & contentious == 1

** add flag for diector election
rename agendageneraldesc temp
gen agendageneraldesc = lower(temp)
drop temp
gen direlection = 0
replace direlection = 1 if strpos(agendageneraldesc, "elect") & strpos(agendageneraldesc, "director")

keep countryofinc $firm meetingid sponsor issagendaitemid ballotitemnumber seqnumber meetingdate itemonagendaid voteresult issrec contentious direlection
save "ISS_Results.dta", replace

/* less restrictive match */
sort itemonagendaid 
quietly by itemonagendaid:  gen dup = cond(_N==1,0,_n)

drop if dup > 1 
drop dup*
save "ISS_Results_Short.dta", replace




***** Part 2: Add these info to mutual fund votes
use "MFVote_pseudo.dta", clear
rename *, lower
//assign parent_name by running A4_AssignParentNamesVA

merge m:1 countryofinc meetingid issagendaitemid ballotitemnumber seqnumber meetingdate itemonagendaid using "ISS_Results" 
drop if _merge == 2
drop _merge 

foreach var in $firm sponsor voteresult contested issrec contentious{
	rename `var' `var'_temp
}

drop countryofinc meetingid issagendaitemid ballotitemnumber seqnumber
merge m:1 itemonagendaid using "ISS_Results_Short"
drop if _merge == 2
drop _merge 
foreach var in $firm sponsor voteresult contested issrec contentious{ 
	replace `var'_temp = `var' if missing(`var'_temp)
	drop `var'
	rename `var'_temp `var'
}

drop if missing($firm) | missing($level) 

	/* passive funds */
gen passive = 0
gen tmp = upper(fundname)
foreach word in BLOOMBERG DJ DOW FTSE IDX "IND " INDEX INDX KBW MORNINGSTAR MSCI NASDAQ NYSE RUSSELL "S&P" "S & P" "SANDP" "S AND P" SP STOXX WILSHIRE "100" "400" "500" "600" "900" "1000" "1500" "2000" "5000" { 
	replace passive = 1 if strpos(tmp, "`word'")
} 
drop tmp 


*Part 3: Create LHS Variables 
rename itemonagendaid proposal_id
gsort proposal_id $level
quietly by proposal_id $level:  gen dup = cond(_N==1,0,_n)

******** Flag if a fund did not follow ISS recommendation 
gen tmpi     = 0
replace tmpi = 1 if (issrec=="Abstain"|issrec=="Do Not Vote"|issrec=="Withhold"|issrec=="Against") & (fundvote=="For") // Excluding the case where issrec=="Refer"
replace tmpi = 1 if (issrec=="For") & (fundvote=="Abstain"| fundvote=="Do Not Vote" | fundvote=="Withhold" | fundvote=="None" | fundvote=="Against")
replace tmpi = 1 if issrec=="One Year" & (strpos(fundvote, "Year") & !strpos(fundvote, "One"))
replace tmpi = 1 if issrec=="Two Years" & (strpos(fundvote, "Year") & !strpos(fundvote, "Two"))
replace tmpi = 1 if issrec=="Three Years" & (strpos(fundvote, "Year") & !strpos(fundvote, "Three"))

egen AGSTISS = mean(tmpi), by(proposal_id $level)


* Sponsor
egen temp = mean(tmpi) if sponsor=="Shareholder", by(proposal_id $level)
egen AGSTISS_SH = mean(temp), by(proposal_id $level)
drop temp


* Contentious
egen temp = mean(tmpi) if contentious == 1, by(proposal_id $level)
egen AGSTISS_CT = mean(temp), by(proposal_id $level)
drop temp

egen temp = mean(tmpi) if contentious == 0, by(proposal_id $level)
egen AGSTISS_NCT = mean(temp), by(proposal_id $level)
drop temp


* Contentious * Sponsor 
egen temp = mean(tmpi) if contentious == 1 & sponsor=="Shareholder", by(proposal_id $level)
egen AGSTISS_CT_SH = mean(temp), by(proposal_id $level)
drop temp

* keep unique 
drop if dup > 1 
save "VA_AgainstISS.dta", replace


**** Code to create Less-perfunctory Management Proposals - m&a, say on pay, governance
**** Same as above but instead focus on Management Sponsored Proposals
gen tmp = upper(itemdesc)
gen mna = 0
foreach word in MERGE ACQUIS ACQUIR M&A { 
	replace mna = 1 if strpos(tmp, "`word'")
} 
gen sop = 0
foreach word in STOCK OPTION PAY "GOLDEN PARACHUTE" CLAWBACK COMPENSATION { 
	replace sop = 1 if strpos(tmp, "`word'")
} 
gen gov = 0
foreach word in GOVERNANCE REFORM REGULAT ACCOUNTAB TRANSPAREN OVERSIGHT { 
	replace gov = 1 if strpos(tmp, "`word'")
} 
drop tmp

gen msg = 0
replace msg = 1 if sponsor=="Management" & (mna==1 | sop==1 | gov==1)

egen temp = mean(tmpi) if contentious == 1 & msg==1, by($proposalid $institution)
egen AGSTISS_CT_MSG = mean(temp), by($proposalid $institution)
drop temp
